﻿Public Class frmReserveRooms

    Private dt As DataTable
    Private cdt As DataTable
    Private deRoom As Long
    Private khrAmount As Long
    Private totalCoinAmount As Long
    Private m As Integer
    Private y As Integer
    Private action As String
    Private actType As String() = {"INSERT", "UPDATE"}
    Private fmclose As Boolean = True
    Private sample As Boolean = False

    ' Real stock data adapter
    Private sheetDa As New DSVaultingTableAdapters.rr_monthly_reserve_sheet_stocksTableAdapter
    Private coinDa As New DSVaultingTableAdapters.rr_monthly_reserve_coin_stocksTableAdapter
    Private sdDa As New DSVaultingTableAdapters.rr_reserve_sheet_stock_detailsTableAdapter
    Private cdDa As New DSVaultingTableAdapters.rr_reserve_coin_stock_detailsTableAdapter

    ' Sample stock data adapter
    Private samSheetDa As New DSVaultingTableAdapters.rr_monthly_sample_sheet_stocksTableAdapter
    Private samCoinDa As New DSVaultingTableAdapters.rr_monthly_sample_coin_stocksTableAdapter
    Private samSheetDeDa As New DSVaultingTableAdapters.rr_sample_sheet_stock_detailsTableAdapter
    Private samCoinDeDa As New DSVaultingTableAdapters.rr_sample_coin_stock_detailsTableAdapter

    Private Sub frmReserveRoom_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        m = frmMonthYearPicker.uxMonthcbb.SelectedIndex
        y = Integer.Parse(frmMonthYearPicker.uxYearcbb.Text)
        deRoom = frmReserveRoomslc.uxRoomCbx.SelectedValue

        '
        ' Set label title
        '
        uxRRHeaderLbl.Text = "ឃ្លាំងត្រៀមបំរុង" & frmReserveRoomslc.uxBranchCbx.Text & " " & _
                                frmReserveRoomslc.uxRoomCbx.Text & _
                                " (ខែ " & frmMonthYearPicker.getKHMonth(m) & " " & y & ")"

        If frmReserveRoomslc.sample Then
            uxIsSampleLbl.Visible = True
            sample = True
        End If

        '
        ' Validate data for current month
        ' If data exist then load existing data
        ' Otherwise key in new data
        '
        If checkCurrentMonth() Then
            If frmLogin.UserRole = frmLogin.RoleAdmin Then
                action = actType(1)
            Else
                uxNoteDgv.Columns("NoteSack20").ReadOnly = True
                uxNoteDgv.Columns("NoteSack30").ReadOnly = True
                uxCoinDgv.Columns("SackTwenty").ReadOnly = True
                uxCoinDgv.Columns("SackThirty").ReadOnly = True
                uxSaveChangeBtn.Enabled = False
            End If

            If Not sample Then
                dt = sdDa.GetDataByRelatedFields(deRoom, m, y)
                cdt = cdDa.GetDataByRelatedFields(deRoom, m, y)
            Else
                dt = samSheetDeDa.GetDataByRelateFields(deRoom, m, y)
                cdt = samCoinDeDa.GetDataByRelateFields(deRoom, m, y)
            End If

            khrAmount = dt.Rows(0).Item("TOTAL_AMOUNT")
            uxKHRTotalTbx.Text = FormatNumber(khrAmount, 0)
            totalCoinAmount = cdt.Rows(0).Item("TOTAL_AMOUNT")
            uxCOINTotalTbx.Text = FormatNumber(totalCoinAmount, 0)

            updateTotalAmount()

        Else
            If frmLogin.UserRole = frmLogin.RoleAdmin Or frmLogin.UserRole = frmLogin.RoleUser Then
                action = actType(0)
            Else
                uxNoteDgv.Columns("NoteSack20").ReadOnly = True
                uxNoteDgv.Columns("NoteSack30").ReadOnly = True
                uxCoinDgv.Columns("SackTwenty").ReadOnly = True
                uxCoinDgv.Columns("SackThirty").ReadOnly = True
                uxSaveChangeBtn.Enabled = False
            End If

            If Not sample Then
                dt = sheetDa.GetDataBy
                cdt = coinDa.GetDataByCointOnly
            Else
                dt = samSheetDa.GetDataBy
                cdt = samCoinDa.GetDataCoinOnly
            End If

            ' Fill KHR SHeet DataTable with some selected values
            Dim i As Integer
            For i = 0 To dt.Rows.Count - 1 Step 1
                dt.Rows(i)("ROOM_ID") = deRoom
                dt.Rows(i)("STOCK_MONTH") = m
                dt.Rows(i)("STOCK_YEAR") = y
            Next

            ' Fill COIN DataTable with some selected values
            Dim x As Integer
            For x = 0 To cdt.Rows.Count - 1 Step 1
                cdt.Rows(x)("ROOM_ID") = deRoom
                cdt.Rows(x)("STOCK_MONTH") = m
                cdt.Rows(x)("STOCK_YEAR") = y
            Next
        End If

        uxNoteDgv.DataSource = dt
        uxCoinDgv.DataSource = cdt
    End Sub

    Private Sub uxSaveChangeBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles uxSaveChangeBtn.Click
        Dim sql As New ArrayList()

        Dim sheetStockDetail As String = "rr_reserve_sheet_stock_details"
        Dim coinStockDetail As String = "rr_reserve_coin_stock_details"
        Dim sheetStock As String = "rr_monthly_reserve_sheet_stocks"
        Dim coinStock As String = "rr_monthly_reserve_coin_stocks"

        If sample Then
            sheetStockDetail = "rr_sample_sheet_stock_details"
            coinStockDetail = "rr_sample_coin_stock_details"
            sheetStock = "rr_monthly_sample_sheet_stocks"
            coinStock = "rr_monthly_sample_coin_stocks"
        End If

        If action = actType(0) Then ' DO INSERT
            Dim i As Integer
            Dim stockCoinID As Long = coinDa.ScalarQueryID
            Dim stockSheetID As Long = sheetDa.ScalarQueryID
            '
            ' Insert statement for reserve_sheet_stock
            '
            sql.Add("INSERT INTO " & sheetStock & " VALUES (" & stockSheetID & _
                    "," & uxNoteDgv.Rows(0).Cells("ROOM_ID").Value & _
                    "," & khrAmount & _
                    "," & m & "," & y & ")")
            For i = 0 To uxNoteDgv.RowCount - 1 Step 1
                sql.Add("INSERT INTO " & sheetStockDetail & " VALUES (" & stockSheetID & _
                        "," & uxNoteDgv.Rows(i).Cells("SERIESID").Value & _
                        "," & uxNoteDgv.Rows(i).Cells("NoteSack20").Value & _
                        "," & uxNoteDgv.Rows(i).Cells("NoteSack30").Value & _
                        "," & uxNoteDgv.Rows(i).Cells("Amount").Value & _
                        ",NOW())")
            Next

            '
            ' Insert statement for reserve_coin_stock
            '
            sql.Add("INSERT INTO " & coinStock & " VALUES (" & stockCoinID & _
                    "," & uxCoinDgv.Rows(0).Cells("RoomID").Value & _
                    "," & totalCoinAmount & _
                    "," & m & "," & y & ")")
            For i = 0 To uxCoinDgv.RowCount - 1 Step 1
                sql.Add("INSERT INTO " & coinStockDetail & " VALUES (" & stockCoinID & _
                        "," & uxCoinDgv.Rows(i).Cells("CoinID").Value & _
                        "," & uxCoinDgv.Rows(i).Cells("SackTwenty").Value & _
                        "," & uxCoinDgv.Rows(i).Cells("SackThirty").Value & _
                        "," & uxCoinDgv.Rows(i).Cells("CoinAmount").Value & _
                        ",NOW())")
            Next

        ElseIf action = actType(1) Then ' DO UPDATE
            Dim i As Integer
            Dim stockCoinID As Long = coinDa.ScalarQueryIDByRelateFields(deRoom, m, y)
            Dim stockSheetID As Long = sheetDa.ScalarQueryIDBYRelateFields(deRoom, m, y)
            '
            ' Update statement for reserve_sheet_stock
            '
            sql.Add("UPDATE " & sheetStock & " SET " & _
                    " TOTAL_AMOUNT =" & khrAmount & _
                    " WHERE STOCK_ID=" & stockSheetID)

            sql.Add("DELETE FROM " & sheetStockDetail & " WHERE STOCK_ID=" & stockSheetID)

            For i = 0 To uxNoteDgv.RowCount - 1 Step 1
                sql.Add("INSERT INTO " & sheetStockDetail & " VALUES (" & stockSheetID & _
                        "," & uxNoteDgv.Rows(i).Cells("SERIESID").Value & _
                        "," & uxNoteDgv.Rows(i).Cells("NoteSack20").Value & _
                        "," & uxNoteDgv.Rows(i).Cells("NoteSack30").Value & _
                        "," & uxNoteDgv.Rows(i).Cells("Amount").Value & _
                        ",NOW())")
            Next

            '
            ' Insert statement for reserve_coin_stock
            '
            sql.Add("UPDATE " & coinStock & " SET " & _
                    " TOTAL_AMOUNT =" & totalCoinAmount & _
                    " WHERE STOCK_ID=" & stockCoinID)

            sql.Add("DELETE FROM " & coinStockDetail & " WHERE STOCK_ID=" & stockCoinID)

            For i = 0 To uxCoinDgv.RowCount - 1 Step 1
                sql.Add("INSERT INTO " & coinStockDetail & " VALUES (" & stockCoinID & _
                        "," & uxCoinDgv.Rows(i).Cells("CoinID").Value & _
                        "," & uxCoinDgv.Rows(i).Cells("SackTwenty").Value & _
                        "," & uxCoinDgv.Rows(i).Cells("SackThirty").Value & _
                        "," & uxCoinDgv.Rows(i).Cells("CoinAmount").Value & _
                        ",NOW())")
            Next
        End If

        Dim lg As New LogGenerator
        Dim st As String = ""
        For Each s As String In sql
            st += s & ";" & vbNewLine
        Next
        lg.writeLog(st)
        '
        ' Execute Batch Insert
        '
        If action = "" Then
            MsgBox("លោកអ្នកមិនមានសិទ្ធគ្រប់គ្រាន់ទេ!", MsgBoxStyle.Exclamation)
        Else
            If MsgBox("តើលោកអ្នកពិតជាចង់រក្សាទុកអ្វីដែលបានកែប្រែមែនទេ??", MsgBoxStyle.YesNo) = DialogResult.Yes Then
                If executeBatch(sql) Then
                    MsgBox("ទិន្នន័យត្រួវបានរក្សាទុក")
                Else
                    MsgBox(DbConnect.errMsg)
                End If
            End If
        End If
    End Sub

    Private Sub txtEdit_Keypress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
        If IsNumeric(e.KeyChar.ToString()) _
        Or e.KeyChar = ChrW(Keys.Back) _
        Or e.KeyChar = "." Then
            e.Handled = False 'if numeric display
        Else
            e.Handled = True  'if non numeric don't display
        End If
    End Sub

    Private Sub uxUSDTotalTbx_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles uxCOINTotalTbx.TextChanged
        updateTotalAmount()
    End Sub

    Private Sub uxKHRTotalTbx_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles uxKHRTotalTbx.TextChanged
        updateTotalAmount()
    End Sub

    Private Sub updateTotalAmount()
        uxTotalTbx.Text = FormatNumber(khrAmount + totalCoinAmount, 0)
    End Sub

    Private Sub uxCancelBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles uxCancelBtn.Click
        fmclose = False
        If MsgBox("តើអ្នកពិតជាចង់ចាកចេញមែន រឺ ទេ?", MsgBoxStyle.YesNo) = DialogResult.Yes Then
            Me.Close()
            frmRoomSelecting.Enabled = True
        End If
    End Sub

    Private Function updateNoteAmount(ByVal dgv As DataGridView, _
                                  ByVal fstCol As String, _
                                  ByVal sndCol As String, _
                                  ByVal thdCol As String, _
                                  ByVal amount As String) As Long
        Dim sum As Long
        Dim total As Long = 0
        Dim val As Long
        Dim qt20 As Long
        Dim qt30 As Long
        Dim i As Integer

        For i = 0 To dgv.Rows.Count - 1 Step 1
            Try
                val = If(IsNumeric(dgv.Rows(i).Cells(fstCol).Value), dgv.Rows(i).Cells(fstCol).Value, 0)
                qt20 = If(IsNumeric(dgv.Rows(i).Cells(sndCol).Value), dgv.Rows(i).Cells(sndCol).Value, 0)
                qt30 = If(IsNumeric(dgv.Rows(i).Cells(thdCol).Value), dgv.Rows(i).Cells(thdCol).Value, 0)
                sum = ((qt30 * 30 * 1000) + (qt20 * 20 * 1000)) * val
            Catch ex As Exception
                MsgBox("The value is too big!")
            End Try
            dgv.Rows(i).Cells(sndCol).Value = qt20
            dgv.Rows(i).Cells(thdCol).Value = qt30
            dgv.Rows(i).Cells(amount).Value = sum
            total += sum
        Next
        dgv.Update()
        Return total
    End Function

    '
    ' Checking data for current month
    ' If data exist return true Otherwise false
    '
    Private Function checkCurrentMonth() As Boolean
        Dim val As Integer
        If Not sample Then
            val = sheetDa.ScalarQuerySelectMY(m, y, deRoom)
        Else
            val = samSheetDa.ScalarQuerySelectByMY(m, y, deRoom)
        End If

        If Not val = 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    Private Sub frmReserveRoom_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        If fmclose = True Then
            If MsgBox("តើអ្នកពិតជាចង់ចាកចេញមែន រឺ ទេ?", MsgBoxStyle.YesNo) = DialogResult.Yes Then
                e.Cancel = False
                frmRoomSelecting.Enabled = True
            Else
                e.Cancel = True
            End If
        End If
    End Sub

    Private Sub uxNoteDgv_EditingControlShowing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles uxNoteDgv.EditingControlShowing
        Dim txtEdit As TextBox = e.Control
        'remove any existing handler
        RemoveHandler txtEdit.KeyPress, AddressOf txtEdit_Keypress
        AddHandler txtEdit.KeyPress, AddressOf txtEdit_Keypress
    End Sub

    Private Sub uxNoteDgv_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles uxNoteDgv.CellEndEdit
        khrAmount = updateNoteAmount(uxNoteDgv, "NoteType", "NoteSack20", "NoteSack30", "Amount")
        uxKHRTotalTbx.Text = FormatNumber(khrAmount, 0)
    End Sub

    Private Sub uxCoinDgv_CellEndEdit_1(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles uxCoinDgv.CellEndEdit
        totalCoinAmount = updateNoteAmount(uxCoinDgv, "CoinType", "SackTwenty", "SackThirty", "CoinAmount")
        uxCOINTotalTbx.Text = FormatNumber(totalCoinAmount, 0)
    End Sub

    Private Sub uxCoinDgv_EditingControlShowing_1(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles uxCoinDgv.EditingControlShowing
        Dim txtEdit As TextBox = e.Control
        'remove any existing handler
        RemoveHandler txtEdit.KeyPress, AddressOf txtEdit_Keypress
        AddHandler txtEdit.KeyPress, AddressOf txtEdit_Keypress
    End Sub
End Class